CREATE TABLE [dbo].[PromotionRest](
	[PromotionRestId] [int] IDENTITY(1,1) NOT NULL,
	[PromotionId] [int] NOT NULL,
	[DateRest] [datetime] NOT NULL,
	[Amount] [money] NOT NULL,
 CONSTRAINT [PK_PromotionRest] PRIMARY KEY CLUSTERED 
(
	[PromotionRestId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PromotionRest]  WITH CHECK ADD  CONSTRAINT [FK_PromotionRest_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionRest] CHECK CONSTRAINT [FK_PromotionRest_Promotion]
GO
CREATE FUNCTION [dbo].[fnBusiness_PromotionRest_Available]
(
	@businessId int
)
RETURNS money
AS
BEGIN
	declare @result money
	
	select @result = isnull(sum(PromotionRest.Amount), 0)
	from PromotionRest (nolock) left join Promotion (nolock) on PromotionRest.PromotionId = Promotion.PromotionId
	where Promotion.BusinessId = @businessId
	
	select @result = @result - isnull(sum(PromotionPay.Amount), 0)
	from PromotionPay (nolock) inner join Promotion (nolock) on PromotionPay.PromotionId = Promotion.PromotionId
	where (Promotion.BusinessId = @businessId)
		and (PromotionPay.CreditCardNo is null)
		
	return @result
END
GO
CREATE FUNCTION fnPromotionRest_Calc
(
	@promotionId int
)
RETURNS money
AS
BEGIN
	declare @result money
	
	-- pays
	select @result = Promotion.CampaignBudget
	from Promotion (nolock)
	where (Promotion.PromotionId = @PromotionId)
	
	-- minus pushes cost
	select @result = @result - isnull(sum(RatePerPush), 0)
	from PromotionPush (nolock)
	where (PromotionPush.PromotionId = @PromotionId)
	
	-- minus promotion cost
	select @result = @result - isnull(sum(FixedCost), 0)
	from PromotionCost (nolock)
	where (PromotionCost.PromotionId = @PromotionId)
	
	if @result < 1
		set @result = 0
	else begin
		set @result = round(@result, 0, 1)
	end 
			
	return @result
END
GO
create procedure spPromotionRest_Add
(
	@PromotionId int
)
as
begin
	set nocount on
	
	declare @promoSaldo money
	set @promoSaldo = dbo.fnPromotionRest_Calc(@promotionId)
	
	if (@promoSaldo > 0)
		insert into PromotionRest
		(PromotionId, DateRest, Amount)
		values (@PromotionId, GETUTCDATE(), @promoSaldo)
end
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_Stop]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			UPDATE Promotion
			SET StatusCode = 'COM'
			WHERE (PromotionId = @PromotionId)
			
			exec spPromotionRest_Add @PromotionId = @PromotionId
		END	
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
go
ALTER PROCEDURE [dbo].[spPromotion_StartStop]
(
	@PromotionId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @Now DATETIME
	SET @Now = GETUTCDATE()
	
	declare @promotionIdCurr int
	
	CREATE TABLE #tmpPromotion1
	(
		PromotionId INT,
		Gmt INT
	)
	
	CREATE TABLE #tmpPromotion2
	(
		PromotionId INT,
		Gmt INT,
		WillStop bit default(0)
	)
	
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				)
	ELSE
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				)
	
	-- Starts promotion
	UPDATE Promotion
	SET StatusCode = 'ACT'
	FROM #tmpPromotion1
	WHERE (Promotion.PromotionId = #tmpPromotion1.PromotionId)

	-- Make pay for starts promotion
	INSERT INTO PromotionCost
	(PromotionId, DateSubmit, FixedCost)
	SELECT Promotion.PromotionId, GETUTCDATE(), Promotion.FixedCost
	FROM Promotion (NOLOCK) INNER JOIN #tmpPromotion1 ON Promotion.PromotionId = #tmpPromotion1.PromotionId
					
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion2
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion2
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			
	-- Mark promo for stop
	update #tmpPromotion2
	set WillStop = 1
	from Promotion
	where (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId = 5) 
		AND ((Promotion.BaseCostTimingEnds <= @Now) or (DATEDIFF(DAY, Promotion.BaseCostTimingStarts, GETDATE()) > 7))
		
	update #tmpPromotion2
	set WillStop = 1
	from Promotion
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId  IN (2, 3, 4)) 
		AND (DATEDIFF(DAY, Promotion.BaseCostTimingScheduledAt, GETDATE()) > 7)
	
	/*-- Stop promotion
	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId = 5) 
		AND ((Promotion.BaseCostTimingEnds <= @Now) or (DATEDIFF(DAY, Promotion.BaseCostTimingStarts, GETDATE()) > 7))

	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId  IN (2, 3, 4)) 
		AND (DATEDIFF(DAY, Promotion.BaseCostTimingScheduledAt, GETDATE()) > 7)*/
		
	-- Stop promotion
	update Promotion
	set StatusCode = 'COM'			
	from #tmpPromotion2
	where (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		and (#tmpPromotion2.WillStop = 1) 
		
	-- Calc rest and save rest
	declare cursorStopped cursor for 
	select PromotionId
	from #tmpPromotion2
	where WillStop = 1
	
	open cursorStopped
	fetch from cursorStopped into @promotionIdCurr
	
	while @@FETCH_STATUS = 0
	begin
		exec spPromotionRest_Add @PromotionId = @promotionIdCurr
		fetch from cursorStopped into @promotionIdCurr
	end
	
	close cursorStopped
	deallocate cursorStopped

	DROP TABLE #tmpPromotion1
	DROP TABLE #tmpPromotion2
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	@LocalTime DATETIME = NULL,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	declare @promotionIdCurr int
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT,
		RatePerPush MONEY,
		Lat FLOAT,
		Lon FLOAT,
		WillStop bit default(0)
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, RatePerPush, Lat, Lon)
			SELECT PromotionId, RatePerPush, Latitude, Longitude
			FROM PromotionPushQueue (NOLOCK)
			WHERE UserId = @UserId
			
			if @@error <> 0 return
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions pp (nolock) INNER JOIN Promotion (NOLOCK) ON pp.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, ROUND(AVG(CONVERT(FLOAT, Rating)), 0) AS AvgRating 
													FROM BusinessReview (NOLOCK)
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
													--WHERE pp.UserId = @UserId
							FOR XML PATH ('Promotion'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETUTCDATE(), Lat, Lon, RatePerPush
			FROM #tmpPromotions
			--WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
			if @@error <> 0 return
			
			DELETE
			FROM PromotionPushQueue
			WHERE (UserId = @UserId)
				AND (PromotionId IN (SELECT PromotionId FROM #tmpPromotions))

			if @@error <> 0 return
			
			update #tmpPromotions
			set WillStop = 1
			from Promotion LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)
						
			/*UPDATE Promotion
			SET StatusCode = 'COM'
			FROM #tmpPromotions LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON #tmpPromotions.PromotionId = vwPush.PromotionId
								LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON #tmpPromotions.PromotionId = vwPromotionCost.PromotionId
			WHERE (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)*/
				
			update Promotion
			set StatusCode = 'COM'
			from #tmpPromotions				
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				and (#tmpPromotions.WillStop = 1)
				
				
			-- Calc rest and save rest
			declare cursorStopped cursor for 
			select PromotionId
			from #tmpPromotions
			where WillStop = 1
			
			open cursorStopped
			fetch from cursorStopped into @promotionIdCurr
			
			while @@FETCH_STATUS = 0
			begin
				exec spPromotionRest_Add @PromotionId = @promotionIdCurr
				fetch from cursorStopped into @promotionIdCurr
			end
			
			close cursorStopped
			deallocate cursorStopped
			
			if @@error <> 0 return
			
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_GetPayInfo]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF (@IdResult = 1)
		IF (NOT EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId))
			SET @IdResult = -1

	IF @IdResult = 1
		SELECT @IdResult AS IdResult, 
			Promotion.CampaignBudget,
			Business.CreditCardExpDate, Business.CreditCardName, Business.CreditCardNo, Business.CreditCardPin,
			Promotion.Name, 
			dbo.fnBusiness_PromotionRest_Available(@BusinessId) as PromoRest
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE Promotion.PromotionId = @PromotionId
	ELSE
		SELECT @IdResult AS IdResult
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_Submit]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@SummaPay money,
	@SummaRest money
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			UPDATE Promotion
			SET
				Ready = 1,
				PromotionNumber = (select isnull(max(PromotionNumber) + 1, 1) from Promotion (nolock) WHERE BusinessId = @BusinessId),
				CheckinCode = CONVERT (VARCHAR(4), (1000+ ABS(CHECKSUM(NewId())) % 8999)) 
			WHERE (PromotionId = @PromotionId)
			
			EXEC spPromotion_StartStop @PromotionId = @PromotionId
			
			SELECT @CampaignBudget = ISNULL(CampaignBudget, 0)
			FROM Promotion (NOLOCK)
			WHERE (PromotionId = @PromotionId)
			
			-- Temporary write to pay
			SELECT @CreditCardNo = CreditCardNo
			FROM Business (NOLOCK)
			WHERE (BusinessId = @BusinessId)
						
			IF @CampaignBudget > 0
			begin
				if @SummaPay > 0
					INSERT INTO PromotionPay
					(PromotionId, DatePay, Amount, CreditCardNo)
					VALUES (@PromotionId, GETUTCDATE(), @SummaPay, @CreditCardNo)
					
				if @SummaRest > 0
					insert into PromotionPay
					(PromotionId, DatePay, Amount)
					values (@PromotionId, GETUTCDATE(), @SummaRest)
			end
			----
		END	
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
go
ALTER FUNCTION [dbo].[fnTransaction_List]
(
  @BusinessId INT,
  @PromotionId INT
)
RETURNS @retList TABLE
(
	RowId INT IDENTITY(1, 1),
	PromotionId INT,
	DateOperation DATETIME,
	TypeOperation VARCHAR(50),
	Amount MONEY,
	TransactionId INT,
	SortFlag INT,
	Balance MONEY
)	
AS  
BEGIN
	INSERT INTO @retList
	(PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag)
		
	SELECT PromotionPush.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0) AS DateOperation, 
		'Charge', -SUM(PromotionPush.RatePerPush), -1 AS TransactionId, 2 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPush (NOLOCK) ON Promotion.PromotionId = PromotionPush.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
	GROUP BY PromotionPush.PromotionId, DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0)
		
	UNION ALL
	
	SELECT PromotionCost.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionCost.DateSubmit)), 0) AS DateOperation, 
		'FlatFee', -PromotionCost.FixedCost, PromotionCost.PromotionCostId AS TransactionId, 1 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionCost (NOLOCK) ON Promotion.PromotionId = PromotionCost.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
				
	UNION ALL
	
	SELECT PromotionPay.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPay.DatePay)), 0) AS DateOperation, 
		'CCard', PromotionPay.Amount, PromotionPay.PromotionPayId AS TransactionId, 0 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPay (NOLOCK) ON Promotion.PromotionId = PromotionPay.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
		and (PromotionPay.CreditCardNo is not null)
		
	ORDER BY PromotionId, DateOperation, SortFlag, TransactionId
		
	DECLARE @RowId INT, @PromotionIdLocal INT, @PromotionIdPrev INT, @Amount MONEY, @RunningTotal MONEY
	SET @PromotionIdPrev = 0
		
	DECLARE cursorOperations 
	CURSOR FOR 
	SELECT RowId, Amount, PromotionId
	FROM @retList
	ORDER BY RowId 
		
	OPEN cursorOperations
	FETCH FROM cursorOperations INTO @RowId, @Amount, @PromotionIdLocal
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @PromotionIdLocal <> @PromotionIdPrev
			SET @RunningTotal = 0
				
		SET @RunningTotal = @RunningTotal + @Amount
		SET @PromotionIdPrev = @PromotionIdLocal
			
		UPDATE @retList 
		SET Balance = @RunningTotal 
		WHERE RowId = @RowId
			
		FETCH FROM cursorOperations INTO @RowId, @Amount, @PromotionIdLocal
	END
		
	CLOSE cursorOperations
	DEALLOCATE cursorOperations
	
	RETURN
END
go
ALTER PROCEDURE [dbo].[spBusiness_GetCreditCardTransaction]
(
	@TransactionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	select @transactionId as TransactionId
		,pp.DatePay as [Date]
		,'CCard' as [Type]
		,pp.Amount
		,p.RatePerPush
		,p.FixedCost
		,case when p.PromotionNumber < 10 then '0' else '' end + cast(p.PromotionNumber as varchar) AS [Description]
		,p.Name as PromotionName
		,'233453454XCE' as ConfirmationNo
	from Promotion p inner join PromotionPay pp on pp.PromotionId = p.PromotionId
	where (pp.PromotionPayId = @transactionId)
		and (pp.CreditCardNo is not null)
END
go
alter table PromotionPay
alter column CreditCardNo varchar(20) null
go